import pandas as pd
from openpyxl import Workbook

# 手动整理的表结构数据（每个表是一个列表）
tables = {

    "ban": [
        ["user_id", "被封禁用户的编号"],
        ["release_time", "解封时间（同时也是主键）"],
        ["length_by_day", "封禁时长（以天为单位）"],
        ["reason", "管理员填写的封禁理由"],
        ["block", "封禁的区块（枚举值：forum, errand），默认为 forum"],
    ],

    "blog": [
        ["creator", "发帖用户编号（外键引用 user.id）"],
        ["id", "帖子唯一标识（主键）"],
        ["title", "帖子标题（最大长度50）"],
        ["content", "帖子内容"],
        ["send_time", "发布时间，默认为当前时间"],
        ["visibility", "可见性（枚举值：admin, sender, visible），默认为 visible"],
    ],

    "charge": [
        ["user_id", "充值用户编号"],
        ["id", "充值记录唯一标识（主键）"],
        ["charge_time", "充值时间"],
        ["amount", "充值金额"],
    ],

    "complaint": [
        ["id", "投诉记录唯一标识（主键）"],
        ["sender_id", "投诉人编号"],
        ["title", "投诉标题（可为空）"],
        ["description", "投诉描述"],
        ["complaint_block", "投诉对象类型（枚举值：FORUM_BLOG, FORUM_REPLY, SHOP, ERRAND）"],
        ["complained_id", "被投诉对象编号"],
        ["result", "处理结果（可为空）"],
    ],

    "errand": [
        ["id", "任务编号（主键）"],
        ["sender_id", "发布者编号（外键引用 user.id）"],
        ["title", "任务标题（最大长度20）"],
        ["errand_description", "任务描述"],
        ["fee", "报酬金额"],
        ["start_location", "起点位置"],
        ["end_location", "终点位置"],
        ["latest_arrival_time", "最晚送达时间"],
        ["acceptor_id", "接单人编号（可为空）（外键引用 user.id）"],
        ["status", "任务状态（枚举值：NORMAL, COMPLETED, CONFIRMED, AUTO_CONFIRMED, EXPIRED, CANCELLED），默认为 NORMAL"],
    ],

    "error": [
        ["id", "错误日志编号（主键、自增）"],
        ["content", "错误信息内容"],
    ],

    "good": [
        ["id", "商品编号（主键）"],
        ["name", "商品名称"],
        ["description", "商品描述（可为空）"],
        ["shop", "所属店铺名称（外键引用 shop.name）"],
        ["price", "商品价格"],
    ],

    "good_transaction": [
        ["id", "交易编号（主键）"],
        ["good_id", "商品编号（外键引用 good.id）"],
        ["amount", "交易数量"],
        ["order_id", "订单编号（可为空，若为空表示是商家补货）"],
        ["time", "交易时间"],
    ],

    "like_blog": [
        ["blog_id", "被点赞的帖子编号（外键引用 blog.id）"],
        ["liker", "点赞用户编号（外键引用 user.id）"],
        ["like_time", "点赞时间，默认为当前时间"],
    ],

    "reply": [
        ["id", "评论编号（主键、自增）"],
        ["blog_id", "关联的帖子编号（外键引用 blog.id）"],
        ["sender", "评论人编号（外键引用 user.id）"],
        ["content", "评论内容"],
        ["send_time", "评论时间，默认为当前时间"],
        ["parent_id", "父评论ID（为空则表示是一级评论）"],
    ],

    "shop": [
        ["name", "店铺名称（主键）"],
        ["description", "店铺描述（可为空）"],
        ["password", "店铺密码（登录使用）"],
    ],

    "shop_order": [
        ["id", "订单编号（主键）"],
        ["shopper_id", "下单用户编号（外键引用 user.id）"],
        ["shop", "所属店铺名称（外键引用 shop.name）"],
    ],

    "user": [
        ["id", "用户编号（主键）"],
        ["name", "用户名字"],
        ["phone_number", "手机号（11位）"],
        ["balance", "账户余额，默认为 0"],
    ],
}

# 创建 Excel writer 对象
with pd.ExcelWriter("campus_aid_schema_by_table.xlsx", engine="openpyxl") as writer:
    for table_name, fields in tables.items():
        # 构建 DataFrame
        df = pd.DataFrame(fields, columns=["字段名", "说明"])
        # 写入到单独的 sheet
        df.to_excel(writer, index=False, sheet_name=table_name[:31])  # Excel sheet 名称不能超过 31 字符

print("✅ 已成功生成 Excel 文件：campus_aid_schema_by_table.xlsx")